<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;

class Apcaiwu extends Common
{

	public function detail($hid){
		
	}
	public function apHexiaoquery($page=1,$list=10){
			//接收数据
    	$request = Request::instance();

    	
    	$data=$request->post();
		$where="ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and (ahp.is_deleted=0 or ahp.is_deleted is null) ";

		if($data){
    		
    		if(trim($data["gys_no"]))
    			$where.="and g.no='{$data["gys_no"]}' ";
    		if(trim($data["gys_name"]))
    			$where.="and g.gys_name='{$data["gys_name"]}' ";
    		
    	}else{
    		$data=["gys_name"=>"","gys_no"=>""];
    	}

    	$sum=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where($where)
		->group("g.id,g.no")
    	->count();


    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;





		$detail=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where($where)
		->group("g.id")
		->order("g.id")
    	->limit($start,$list)
		->field("g.no as gys_no,g.gys_name,sum(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100) as pizhun,g.id")
		->select();
		

		$gids=[];
		for($i=0;$i<count($detail);$i++){
			$gids[]=$detail[$i]["id"];
		}
		$gidstr=implode(',',$gids);
		$yihe=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0  and g.id in($gidstr) and ahp.is_shengxiao=1" )
		->group("g.id")
		->field("g.id,sum(ahd.hexiao_weishui) as yihe")
		->select();
		//exit;
		$heshenqing=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0  and g.id in($gidstr) and ahp.state in (1,2)" )
		->group("g.id")
		->field("g.id,sum(ahd.hexiao_weishui) as shenqing")
		->select();

		$buhe=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1  and g.id in($gidstr) and apd.buyu_he=1" )
		->group("g.id")
		->field("g.id,sum(apd.pifuzonge-apd.hexiao) as buhe")
		->select();

//echo Db::table("ap_apply")->getLastSql();
		for($i=0;$i<count($detail);$i++){
			//合并已合
			$detail[$i]["yihe"]=0;
			for($j=0;$j<count($yihe);$j++){
				if($detail[$i]["id"]==$yihe[$j]["id"]){
					$detail[$i]["yihe"]=$yihe[$j]["yihe"];break;
				}
			}
			//合并申请
			$detail[$i]["shenqing"]=0;
			for($j=0;$j<count($heshenqing);$j++){
				if($detail[$i]["id"]==$heshenqing[$j]["id"]){
					$detail[$i]["shenqing"]=$heshenqing[$j]["shenqing"];break;
				}
			}
			$detail[$i]["weihe"]=$detail[$i]["pizhun"]-$detail[$i]["yihe"]-$detail[$i]["shenqing"];
			//合并不予合
			$detail[$i]["buhe"]=0;
			for($j=0;$j<count($buhe);$j++){
				if($detail[$i]["id"]==$buhe[$j]["id"]){
					$detail[$i]["buhe"]=$buhe[$j]["buhe"];break;
				}
			}
		}
		//echo Db::table("ap_apply")->getLastSql();
		$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("data",$data);
		$this->assign("detail",$detail);
		return $this->fetch();
	}
	//已批准金额详情
	public function approvedPage($gid,$page=1,$list=10){
		$gys=Db::table("gys")->where(["id"=>$gid])->find();
		$this->assign("gys",$gys);
		$sum=Db::table("ap_apply")->alias("ap")
    		->join("ap_apply_detail apd","apd.apply_id=ap.id")
    		->join("ap_apply_detail yuanapd","apd.yuanmingxi=yuanapd.id","LEFT")
    		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    		->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    		->join("cg_market_city c","apd.shi=c.id","LEFT")
    		->join("cg_jxs j","apd.jxs=j.id","LEFT")
    		->join("cg_system s","apd.system=s.id","LEFT")
    		->join("cg_net n","apd.net=n.id","LEFT")
    		->join("pro_brands pb","apd.brand=pb.id","LEFT")
    		->join("pro_category pc","apd.category=pc.id","LEFT")
    		->join("pro_series ps","apd.series=ps.id","LEFT")
    		->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    		->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
    		->join("gys g","apd.gys=g.id")
    		->join("cg_paymethod cp","apd.zfid=cp.id")
    		->where("ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and apd.is_deleted=0 and apd.is_actived=1 and apd.gys=$gid")
    		->count();
    			$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
		$applys=Db::table("ap_apply")->alias("ap")
    		->join("ap_apply_detail apd","apd.apply_id=ap.id")
    		->join("ap_apply_detail yuanapd","apd.yuanmingxi=yuanapd.id","LEFT")
    		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    		->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    		->join("cg_market_city c","apd.shi=c.id","LEFT")
    		->join("cg_jxs j","apd.jxs=j.id","LEFT")
    		->join("cg_system s","apd.system=s.id","LEFT")
    		->join("cg_net n","apd.net=n.id","LEFT")
    		->join("pro_brands pb","apd.brand=pb.id","LEFT")
    		->join("pro_category pc","apd.category=pc.id","LEFT")
    		->join("pro_series ps","apd.series=ps.id","LEFT")
    		->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    		->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
    		->join("gys g","apd.gys=g.id")
    		->join("cg_paymethod cp","apd.zfid=cp.id")
    		->where("ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and apd.is_deleted=0 and apd.is_actived=1 and apd.gys=$gid")
    		->order("apd.id desc")->limit($start,$list)
    		->field("apd.id,ap.no,apd.detail_no,apd.is_bu,g.no as gys_no,g.gys_name,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs,s.title as system,n.title as net,pb.brand_name as brand,pc.category_name as category,ps.series_name as series,k3.title as kemu3,ap.id as apid,apd.pifuzonge,apd.start_time,apd.end_time")
    		->select();
    		$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);
    		//print_r($applys);exit;
		return $this->fetch();
	}
	//已核销
	public function vorifiedPage($gid,$page=1,$list=10){
		$gys=Db::table("gys")->where(["id"=>$gid])->find();
		$this->assign("gys",$gys);
		$sum=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    	->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    	->join("cg_market_city c","apd.shi=c.id","LEFT")
    	->join("cg_jxs j","apd.jxs=j.id","LEFT")
    	->join("cg_system s","apd.system=s.id","LEFT")
    	->join("cg_net n","apd.net=n.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id","LEFT")
    	->join("pro_category pc","apd.category=pc.id","LEFT")
    	->join("pro_series ps","apd.series=ps.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 and ahp.is_shengxiao=1 and g.id=$gid")
    	->count();


    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
		
		$applys=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    	->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    	->join("cg_market_city c","apd.shi=c.id","LEFT")
    	->join("cg_jxs j","apd.jxs=j.id","LEFT")
    	->join("cg_system s","apd.system=s.id","LEFT")
    	->join("cg_net n","apd.net=n.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id","LEFT")
    	->join("pro_category pc","apd.category=pc.id","LEFT")
    	->join("pro_series ps","apd.series=ps.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 and ahp.is_shengxiao=1 and g.id=$gid")
		->order("apd.id desc")->limit($start,$list)
		->field("z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs,s.title as system,n.title as net,pb.brand_name as brand,pc.category_name as category,ps.series_name as series,k3.title as kemu3,ahp.no,ahd.he_detail_no,ahp.created_on,ahd.hexiao_weishui,ahp.id,ap.id as apid")
		->select();
		for($i=0;$i<count($applys);$i++){
			$applys[$i]["created_on"]=substr($applys[$i]["created_on"],0,10);
			
		}
$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);
		return $this->fetch();
	}
//申请中
	public function applyingPage($gid,$page=1,$list=10){
		$gys=Db::table("gys")->where(["id"=>$gid])->find();
		$this->assign("gys",$gys);
		$sum=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    	->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    	->join("cg_market_city c","apd.shi=c.id","LEFT")
    	->join("cg_jxs j","apd.jxs=j.id","LEFT")
    	->join("cg_system s","apd.system=s.id","LEFT")
    	->join("cg_net n","apd.net=n.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id","LEFT")
    	->join("pro_category pc","apd.category=pc.id","LEFT")
    	->join("pro_series ps","apd.series=ps.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 and ahp.state in (1,2) and g.id=$gid")
    	->count();
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
		
		$applys=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    	->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    	->join("cg_market_city c","apd.shi=c.id","LEFT")
    	->join("cg_jxs j","apd.jxs=j.id","LEFT")
    	->join("cg_system s","apd.system=s.id","LEFT")
    	->join("cg_net n","apd.net=n.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id","LEFT")
    	->join("pro_category pc","apd.category=pc.id","LEFT")
    	->join("pro_series ps","apd.series=ps.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
		->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id","LEFT")
		->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 and ahp.state in (1,2) and g.id=$gid")
		->order("apd.id desc")->limit($start,$list)
		->field("z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs,s.title as system,n.title as net,pb.brand_name as brand,pc.category_name as category,ps.series_name as series,k3.title as kemu3,ahp.no,ahd.he_detail_no,ahp.created_on,ahd.hexiao_weishui,ahp.id,ap.id as apid")
		->select();
		for($i=0;$i<count($applys);$i++){
			$applys[$i]["created_on"]=substr($applys[$i]["created_on"],0,10);
			
		}
$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);

		return $this->fetch();
	}
	//不予核销
	public function buhe($gid,$page=1,$list=10){
		$gys=Db::table("gys")->where(["id"=>$gid])->find();
		$this->assign("gys",$gys);
		$sum=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    	->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    	->join("cg_market_city c","apd.shi=c.id","LEFT")
    	->join("cg_jxs j","apd.jxs=j.id","LEFT")
    	->join("cg_system s","apd.system=s.id","LEFT")
    	->join("cg_net n","apd.net=n.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id","LEFT")
    	->join("pro_category pc","apd.category=pc.id","LEFT")
    	->join("pro_series ps","apd.series=ps.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and g.id=$gid and apd.buyu_he=1")
    	->count();

    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
			$applys=Db::table("ap_apply")->alias("ap")
		->join("ap_apply_detail apd","ap.id=apd.apply_id")
		->join("gys g","apd.gys=g.id")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
    	->join("cg_market_provice p","apd.sheng=p.id","LEFT")
    	->join("cg_market_city c","apd.shi=c.id","LEFT")
    	->join("cg_jxs j","apd.jxs=j.id","LEFT")
    	->join("cg_system s","apd.system=s.id","LEFT")
    	->join("cg_net n","apd.net=n.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id","LEFT")
    	->join("pro_category pc","apd.category=pc.id","LEFT")
    	->join("pro_series ps","apd.series=ps.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
		->where("ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and g.id=$gid and apd.buyu_he=1")
		->order("apd.id desc")->limit($start,$list)
		->field("z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs,s.title as system,n.title as net,pb.brand_name as brand,pc.category_name as category,ps.series_name as series,k3.title as kemu3,ap.no,apd.detail_no,apd.start_time,apd.end_time,ap.id as apid,apd.pifuzonge,apd.hexiao,(apd.pifuzonge-apd.hexiao) as weihe")
		->select();
		
$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);
		return $this->fetch();
	}
	//Ap已支付
	public function apCost_pay2($page=1,$list=10){
		$request = Request::instance();
    	$data=$request->post();
    	$where="az.is_deleted=0 and ahp.is_deleted=0 and ahp.is_shengxiao=1 and ap.is_shengxiao=1 and ap.tag=0 ";
    	if($data){
    		if($data["no"])
    			$where.="and ahp.no={$data["no"]} ";
    		if(trim($data["title"]))
    			$where.="and ap.title like '%{$data["title"]}%' ";
    		if($data["cname"])
    			$where.="and ec.em_name='{$data["cname"]}' ";
    		if(trim($data["start"]))
    			$where.="and ahp.created_on>='{$data["start"]} 00:00:00' ";
    		if(trim($data["end"]))
    			$where.="and ahp.created_on<='{$data["end"]} 23:59:59' ";
    	}else{
    		$data=["no"=>"","title"=>"","cname"=>"","start"=>"","end"=>""];
    	}
    	$sum=Db::table("ap_zhifu")->alias("az")
    	->join("ap_hexiao_apply ahp","az.hexiao_id=ahp.id")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)
    	->group("az.id,ap.id,ahp.id")
    	->count();
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

    	$applys=Db::table("ap_zhifu")->alias("az")
    	->join("ap_hexiao_apply ahp","az.hexiao_id=ahp.id")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)
    	->group("az.id,ap.id,ahp.id")
    	->order("az.zhifu_day desc,az.hexiao_id")->limit($start,$list)
    	->field("ap.id as apid,ahp.id as ahpid,az.id,ec.em_name as cname,ahp.no,ap.title,sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hexiao,az.zfjin,az.zhifu_day,az.zfpz")
    	->select();
    	$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);
		$this->assign("data",$data);
		$sumjine=Db::table("ap_zhifu")->alias("az")
    	->join("ap_hexiao_apply ahp","az.hexiao_id=ahp.id")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)
    	->sum("az.zfjin");
    	$this->assign("sumjine",$sumjine);
    	//print_r($applys);
    	return $this->fetch();
	}
	//Ap支付处理
	public function apCost_pay($page=1,$list=10){
			//接收数据
    	$request = Request::instance();
    	$data=$request->post();
    	$where="ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 and ahp.is_shengxiao=1 ";
    	if($data){
    		if($data["no"])
    			$where.="and ahp.no={$data["no"]} ";
    		if(trim($data["title"]))
    			$where.="and ap.title like '%{$data["title"]}%' ";
    		if($data["cname"])
    			$where.="and ec.em_name='{$data["cname"]}' ";
    		if(trim($data["start"]))
    			$where.="and ahp.created_on>='{$data["start"]} 00:00:00' ";
    		if(trim($data["end"]))
    			$where.="and ahp.created_on<='{$data["end"]} 23:59:59' ";
    	}else{
    		$data=["no"=>"","title"=>"","cname"=>"","start"=>"","end"=>""];
    	}

    	
    	$sum=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)
    	->group("ahp.id,ap.title")
    	->having("sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100)>min(ahp.zfjine)")
    	->count();

    	$sumzong=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)
    	->group("ahp.id,ap.title")
    	->having("sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100)>min(ahp.zfjine)")
    	->field("sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100)-min(ahp.zfjine) as zong")->select();
    	$summoney=0;
    	for($i=0;$i<count($sumzong);$i++){
    		$summoney+=$sumzong[$i]["zong"];
    	}
    	$this->assign("summoney",$summoney);
    	//exit;
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

$applys=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)
    	->group("ahp.id,ap.title,ap.id")
    	->having("sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100)>min(ahp.zfjine)")
    	->order("ahp.id")->limit($start,$list)
    	->field("ahp.id,ahp.no,ap.title,sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahp.zfjine as yifu,ahp.created_on,ec.em_name as cname,ap.id as apid")
    	->select();
    	$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);
		$this->assign("data",$data);

		return $this->fetch();
	}
	//支付操作
	public function zhifu($hid){
		$cid=Session::get("employee_id");
		$cuser=Db::table('employee')->where(["id"=>$cid])->field('em_name as title,id')->find();
		$this->assign("cuser",$cuser);
		$this->assign("hid",$hid);
    	$apply=Db::table("ap_hexiao_apply")->alias("hd")
    	->join("ti_state s","hd.state=s.id")
    	->join("users uc","hd.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("job_employee je","ec.id=je.employee_id","LEFT")
        ->join("job j","je.job_id=j.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")
    	->where(["hd.id"=>$hid,"de.index"=>1])
    	->field("hd.id,ec.em_name as cname,d.de_name,j.job_name,hd.no,hd.created_on,hd.remark,hd.is_shengxiao")
    	->find();
    	$this->assign("apply",$apply);
    	$gys=Db::table("ap_hexiao_detail")->alias("ahd")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("gys g","apd.gys=g.id")
    	->where("ahd.hexiao_id=$hid")
    	->field("distinct g.gys_name")
    	->select();
    	$tmp=[];
    	for($i=0;$i<count($gys);$i++){
    		$tmp[$i]=$gys[$i]["gys_name"];
    	}
    	$gys_str=implode(',',$tmp);
    	$this->assign("gys_str",$gys_str);
    	$applyde=Db::table("ap_hexiao_detail")->alias("ahd")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("gys g","apd.gys=g.id")
    	->join("pro_brands pb","apd.brand=pb.id","LEFT")
    	->join("pro_category pc","apd.category=pc.id","LEFT")
    	->join("pro_series ps","apd.series=ps.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_feiyongkemu2 k2","k3.pid=k2.id","LEFT")
    	->join("users uc","ap.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("cg_channel_types1 c1","ap.channel_id=c1.id")
        ->join("cg_paymethod cp","ahd.zfid=cp.id")
        
    	->where("ahd.hexiao_id=$hid and apd.is_actived=1 and apd.is_deleted=0")
    	->field("ap.no,apd.detail_no,ec.em_name as cname,ap.id as apid,ahd.he_detail_no,ahp.no as he_no,c1.title as channel,g.gys_name,pb.brand_name as brand,k3.title as kemu3,(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100) as hanshui,apd.hexiao,(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100-apd.hexiao) as weihe,ahd.hexiao_weishui,ahd.has_fp,ahd.fp_no,ahd.dikou,ahd.hexiao_shuilv,(ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as shuijin,(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as he_hanshui,apd.start_time,cp.id as zfid,cp.title as zffs,ahd.beizhu,ahd.id as ahdid ")
    	->select();
    	$this->assign("applyde",$applyde);

    	
    	$all=Db::table("ap_hexiao_detail")->alias("ahd")
    	->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->where("ahd.hexiao_id=$hid and apd.is_actived=1 and apd.is_deleted=0")
    	->field("sum(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100) as hanshui,sum(hexiao) as hexiao,sum(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100-apd.hexiao) as weihe,sum(hexiao_weishui) as hexiao_weishui,sum(ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as shuijin,sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as he_hanshui,ahp.zfjine as zhifu_jine,sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100)-ahp.zfjine as weifu")
    	->find();
    	$this->assign("all",$all);
    	$pay=Db::table("cg_paymethod")->select();
    	$this->assign("pay",$pay);
		return $this->fetch();
	}
	//支付处理
	public function zhifuchuli(){
		$request = Request::instance();
    	$data=$request->post();
    	if($data){
    		//print_r($data);exit;
    	//验证
	    	if(trim($data["zfpz"]))
	    		$adddata["zfpz"]=trim($data["zfpz"]);
	    	else
    			return json_encode(["msg"=>"支付凭证不能为空","state"=>2]);
    		if(trim($data["c_users_id"]))
	    		$adddata["z_users_id"]=trim($data["c_users_id"]);
	    	else
    			return json_encode(["msg"=>"支付人不能为空","state"=>2]);
    		if(trim($data["zhifu_day"]))
	    		$adddata["zhifu_day"]=trim($data["zhifu_day"]);
	    	else
    			return json_encode(["msg"=>"支付人时间不能为空","state"=>2]);
    		if(trim($data["zfjin"]))
	    		$adddata["zfjin"]=trim($data["zfjin"]);
	    	else
    			return json_encode(["msg"=>"支付金额时间不能为空且不能为0","state"=>2]);
    		if($data["zfjin"]>0)
	    		$adddata["zfjin"]=trim($data["zfjin"]);
	    	else
    			return json_encode(["msg"=>"支付金额不能为负数","state"=>2]);
    		$hid=$data["hid"];
    		//生成单号
	    		 $timenow=date("Ymd");
	       		$num=Db::table("ap_zhifu")->where("no like '$timenow%'")->count();
	      		 $num++;
	      		 $strnum=$timenow.sprintf("%05d", $num);
	      		 $adddata["no"]=$strnum;
	      		 $adddata["c_users_id"]=Session::get("login_id");
	      		  $adddata["created_on"]=date("Y-m-d H:i:s");
	      		  $adddata["hexiao_id"]=$data["hid"];
	      		   $adddata["remark"]=$data["remark"];

	      	//验证原单金额
	      	$yuan=Db::table("ap_hexiao_apply")->alias("ahp")
	      	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
	      	->where("ahp.id=$hid")
	      	->field("sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as zong,ahp.zfjine")
	      	->find();
	      		
	      	$yuanzf=round($yuan["zfjine"],2);
	      	$yuanzong=round($yuan["zong"],2);
	      	if($yuanzf+$adddata["zfjin"]>$yuanzong)
	      		return json_encode(["msg"=>"支付金额不能超出未付金额","state"=>2]);

	      	$now=$yuanzf+$adddata["zfjin"];
	      	//print_r($adddata);exit;
	      
	      			$yuande=Db::table("ap_hexiao_apply")->alias("ahp")
			      	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
			      	->join("ap_apply_detail apd","apd.id=ahd.detail_id")
			      	->join("ap_apply ap","apd.apply_id=ap.id")
			      	->where("ahp.id=$hid")
			      	->field("ahd.id,ahp.no as ahpno,ahd.he_detail_no as ahdno,ap.no as apno,apd.detail_no as apdno,apd.id as apdid,apd.gys,(ahd.hexiao_weishui+ahd.hexiao_weishui*hexiao_shuilv/100) as hanshui,ahd.dikou,ahd.hexiao_weishui,(ahd.hexiao_weishui*hexiao_shuilv/100) as shuijin")
			      	->select();
	      
	      
	      	//print_r($data);exit;
	      	$bili=$adddata["zfjin"]/$yuanzong;
	      	//print_r($yuande);exit;
	      	Db::startTrans();
	      	try{

	      		Db::table("ap_hexiao_apply")->where("id=$hid")->update(["zfjine"=>$now]);

	      		$res1=Db::table("ap_zhifu")->insertGetId($adddata);
	      		$listdata=[];
	      		for($i=0;$i<count($yuande);$i++){
	      			$tmp["type"]=1;$tmp["zf_id"]=$res1;$tmp["hx_id"]=$yuande[$i]["id"];$tmp["he_no"]=$yuande[$i]["ahpno"];
	      			$tmp["he_de_no"]=$yuande[$i]["ahdno"];$tmp["apply_detail_id"]=$yuande[$i]["apdid"];$tmp["ap_no"]=$yuande[$i]["apno"];
	      			$tmp["ap_de_no"]=$yuande[$i]["apdno"];$tmp["gys"]=$yuande[$i]["gys"];
	      			$tmp["bcjine"]=$yuande[$i]["hanshui"]*$bili;$tmp["zfren"]=$adddata["z_users_id"];$tmp["zfday"]=$adddata["zhifu_day"];
	      			$tmp["pinzheng"]=$adddata["zfpz"];$tmp["dikou"]=$yuande[$i]["dikou"];$tmp["weishui"]=$yuande[$i]["hexiao_weishui"]*$bili;
	      			$tmp["shuijin"]=$yuande[$i]["shuijin"]*$bili;
	      			for($j=0;$j<count($data["did"]);$j++){
	      				if($yuande[$i]["id"]==$data["did"][$j]){
	      					$tmp["zfid"]=$data["paymethod"][$j];
	      					break;
	      				}
	      			}
	      			$listdata[]=$tmp;
	      		}
	      		//print_r($listdata);exit;
	      		$res2=Db::table("apti_zhifu_list")->insertAll($listdata);
	      	}catch(\Exception $e){
	        	Db::rollBack();
	            return json_encode(["msg"=>"系统繁忙，请稍后","state"=>2]);
        	}
        	if($res1&&$res2){
        		Db::commit();
        		return json_encode(["msg"=>"","state"=>1]);
        	}else{
        		Db::rollBack();
	            return json_encode(["msg"=>"系统繁忙，请稍后","state"=>2]);
        	}

    	}else{
    		return json_encode(["msg"=>"请正确提交","state"=>2]);
    	}
    	
	}
	 //suggest查找员工
	
    public function cnamejson(){
        $data = Db::table('employee')->where(["is_actived"=>1,"is_deleted"=>0])->field('em_name as title,id')->select();

        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
        //员工列表
    public function selectcname(){
    	return $this->fetch();
    }

       //员工树
    public function cnametree(){
    	$bumen=db("department")->select();
    	
    	foreach ($bumen as $k => $v) {
    		# code...
    		$bumen[$k]["id"]=$v["id"];
    		$bumen[$k]["name"]=$v["de_name"];
    		$bumen[$k]["nocheck"]=true;
    		$bumen[$k]["children"]=db("employee e")->join("department_employee de","de.employee_id=e.id")->where("e.is_deleted=0 and e.is_actived=1 and de.index=1 and de.department_id={$v["id"]}")->field("e.id,e.em_name as name")->select();
    	}
    	 echo json_encode($bumen);
    }
    //AP所有核销单查询
    public function all_hexiao_query($list=10,$page=1){
    	$states=Db::table("ti_state")->order("paixu")->select();
    	$this->assign("states",$states);
    	$request = Request::instance();
    	$data=$request->post();
    	$where="ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 ";
		if($data){
    		if($data["no"])
    			$where.="and ahp.no={$data["no"]} ";
    		if(trim($data["title"]))
    			$where.="and ap.title like '%{$data["title"]}%' ";
    		if($data["cname"])
    			$where.="and ec.em_name='{$data["cname"]}' ";
    		if(trim($data["start"]))
    			$where.="and ahp.created_on>='{$data["start"]} 00:00:00' ";
    		if(trim($data["end"]))
    			$where.="and ahp.created_on<='{$data["end"]} 23:59:59' ";
    		if($data["state"])
    			$where.="and ahp.state={$data["state"]} ";
    	}else{
    		$data=["no"=>"","title"=>"","cname"=>"","start"=>"","end"=>"","state"=>0];
    	}
    	$sum=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state s","ahp.state=s.id")
        ->where($where)
        ->group("ahp.id,ap.id,ap.title")
        ->count();
//exit;
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
    	$applys=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state s","ahp.state=s.id")
        ->where($where)
        ->group("ahp.id,ap.id,ap.title")
        ->order("ahp.id desc")->limit($start,$list)
        ->field("ahp.id,ahp.no,ap.title,sum(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahp.zfjine,s.title as statetype,s.id as stateid,ec.em_name as cname,ahp.created_on,ap.id as apid,ahp.is_shengxiao")
        ->select();
        $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);
		$this->assign("data",$data);
    	return $this->fetch();
    }
    //所有核销记录
    public function all_yihe($list=10,$page=1){
    	//费用明细
    	 $feiwhere = [];
         $feiwhere['esuseway'] = ['like','%"6"%'];
        
        $feidata = Db::table('cg_feiyongkemu3')->field('title,id')->where($feiwhere)->select();
        $this->assign("feidata",$feidata);

        $branddata=Db::table("pro_brands")->where("is_actived=1 and is_deleted=0")->field("id,brand_name")->select();
        $this->assign("branddata",$branddata);
    	$request = Request::instance();
    	$data=$request->post();
    	$where="ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 and ahp.is_shengxiao=1 ";
    	
		if($data){
			if($data["heno"])
    			$where.="and ahp.no={$data["heno"]} ";
    		if($data["no"])
    			$where.="and ap.no={$data["no"]} ";
    		if(trim($data["gys_name"]))
    			$where.="and g.gys_name like '%{$data["gys_name"]}%' ";
    		if($data["pbid"])
    			$where.="and pb.id='{$data["pbid"]}' ";
    		if($data["fid"])
    			$where.="and k3.id='{$data["fid"]}' ";
    		if($data["cname"])
    			$where.="and ec.em_name='{$data["cname"]}' ";
    		if(trim($data["start"]))
    			$where.="and ap.created_on>='{$data["start"]} 00:00:00' ";
    		if(trim($data["end"]))
    			$where.="and ap.created_on<='{$data["end"]} 23:59:59' ";
    	
    	}else{
    		$data=["no"=>"","gys_name"=>"","pbid"=>"","fid"=>"","start"=>"","end"=>"","cname"=>"","heno"=>""];
    	}


    	$sum=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("gys g","apd.gys=g.id")
    	->join("pro_brands pb","apd.brand=pb.id")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_paymethod cp","ahd.zfid=cp.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)->group("ahd.id")->count();
    	




    	
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

    	$applys=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("gys g","apd.gys=g.id")
    	->join("pro_brands pb","apd.brand=pb.id")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_paymethod cp","ahd.zfid=cp.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)->group("ahd.id")->limit($start,$list)
    	->field("ap.id as apid,apd.id as apdid,ahd.id,ap.no as apno,ahp.no as ahpno,ahd.he_detail_no,apd.detail_no,g.gys_name,pb.brand_name as brand,k3.title as kemu3,(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100) as touru,ahd.hexiao_weishui,ahd.has_fp,ahd.fp_no,ahd.dikou,ahd.hexiao_shuilv,(ahd.hexiao_shuilv*ahd.hexiao_weishui/100) as shuijin,(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hexiao_hanshui,apd.start_time,cp.title as zffs,ahd.beizhu,ec.em_name as cname")
    	->select();
    	$apdids=[];
    	//$apids=[];
    	for($i=0;$i<count($applys);$i++){
    		if(!in_array($applys[$i]["apdid"],$apdids)){
    			$apdids[]=$applys[$i]["apdid"];
    		}
    		/*if(!in_array($applys[$i]["apid"],$apids)){
    			$apids[]=$applys[$i]["apid"];
    		}*/
    		
    	}
    	$apdidstr=implode(',',$apdids);
    	$yihe=Db::table("ap_apply_detail")->alias("apd")
    	->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id")
    	->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id")
    	->group("apd.id")
    	->where("ahp.is_deleted=0 and apd.id in($apdidstr)")
    	->field("apd.id,sum(hexiao_weishui) as yihe")
    	->select();
    	
    	for($i=0;$i<count($applys);$i++){
    		for($j=0;$j<count($yihe);$j++){
    			if($applys[$i]["apdid"]==$yihe[$j]["id"]){
    				$applys[$i]["yihe"]=$yihe[$j]["yihe"];
    				$applys[$i]["weihe"]=$applys[$i]["touru"]-$applys[$i]["yihe"];


    				break;
    			}
    		}


    	}

    	 $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("applys",$applys);
		$this->assign("data",$data);
    	return $this->fetch();
    }
    //导出所有
    public function daochu_allhexiao(){
    	$where="ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and ahp.is_deleted=0 and ahp.is_shengxiao=1 ";
    		$applys=Db::table("ap_hexiao_apply")->alias("ahp")
    	->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
    	->join("ap_apply_detail apd","ahd.detail_id=apd.id")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("gys g","apd.gys=g.id")
    	->join("pro_brands pb","apd.brand=pb.id")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->join("cg_paymethod cp","ahd.zfid=cp.id")
    	->join("users uc","ahp.c_user_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->where($where)->group("ahd.id")
    	->field("ap.id as apid,apd.id as apdid,ahd.id,ap.no as apno,ahp.no as ahpno,ahd.he_detail_no,apd.detail_no,g.gys_name,pb.brand_name as brand,k3.title as kemu3,(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100) as touru,ahd.hexiao_weishui,ahd.has_fp,ahd.fp_no,ahd.dikou,ahd.hexiao_shuilv,(ahd.hexiao_shuilv*ahd.hexiao_weishui/100) as shuijin,(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hexiao_hanshui,apd.start_time,cp.title as zffs,ahd.beizhu,ec.em_name as cname")
    	->select();
    	$apdids=[];
    	//$apids=[];
    	for($i=0;$i<count($applys);$i++){
    		if(!in_array($applys[$i]["apdid"],$apdids)){
    			$apdids[]=$applys[$i]["apdid"];
    		}
    		
    	}
    	$apdidstr=implode(',',$apdids);
    	$yihe=Db::table("ap_apply_detail")->alias("apd")
    	->join("ap_hexiao_detail ahd","ahd.detail_id=apd.id")
    	->join("ap_hexiao_apply ahp","ahd.hexiao_id=ahp.id")
    	->group("apd.id")
    	->where("ahp.is_deleted=0 and apd.id in($apdidstr)")
    	->field("apd.id,sum(hexiao_weishui) as yihe")
    	->select();
    	
    	for($i=0;$i<count($applys);$i++){
    		for($j=0;$j<count($yihe);$j++){
    			if($applys[$i]["apdid"]==$yihe[$j]["id"]){
    				$applys[$i]["yihe"]=$yihe[$j]["yihe"];
    				$applys[$i]["weihe"]=$applys[$i]["touru"]-$applys[$i]["yihe"];


    				break;
    			}
    		}


    	}
    		$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("AP费用核销记录");
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
     


          $objPHPExcel->getActiveSheet()->mergeCells('A1:W1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "AP费用核销记录");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:W2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

            #标题
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
       	$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);
       	$objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
       	$objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(40);
       	 $objPHPExcel->getActiveSheet()->getStyle('A4:W4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A4:W4')->getFill()->getStartColor()->setARGB('0099CCFF');
         $objPHPExcel->getActiveSheet()->getStyle('A3:W3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:W3')->getFill()->getStartColor()->setARGB('0099CCFF');
         //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

 //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

         $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->mergeCells('A3:A4');
         $objPHPExcel->getActiveSheet()->setCellValue('B3',"核销单号");
        $objPHPExcel->getActiveSheet()->mergeCells('B3:B4');
         $objPHPExcel->getActiveSheet()->setCellValue('C3',"核销明细");
        $objPHPExcel->getActiveSheet()->mergeCells('C3:C4');
          $objPHPExcel->getActiveSheet()->setCellValue('D3',"申请单号");
        $objPHPExcel->getActiveSheet()->mergeCells('D3:D4');
         $objPHPExcel->getActiveSheet()->setCellValue('E3',"申请明细");
        $objPHPExcel->getActiveSheet()->mergeCells('E3:E4');
         $objPHPExcel->getActiveSheet()->setCellValue('F3',"申请人");
        $objPHPExcel->getActiveSheet()->mergeCells('F3:F4');
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"供应商名称");
        $objPHPExcel->getActiveSheet()->mergeCells('G3:G4');
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"品牌名称");
        $objPHPExcel->getActiveSheet()->mergeCells('H3:H4');
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"三级科目名称");
        $objPHPExcel->getActiveSheet()->mergeCells('I3:I4');
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"实际投入");
        $objPHPExcel->getActiveSheet()->mergeCells('J3:L3');
        $objPHPExcel->getActiveSheet()->setCellValue('J4',"实际投入金额");
        $objPHPExcel->getActiveSheet()->setCellValue('K4',"已核销金额");
        $objPHPExcel->getActiveSheet()->setCellValue('L4',"未核销金额");
         $objPHPExcel->getActiveSheet()->setCellValue('M3',"实际投入");
        $objPHPExcel->getActiveSheet()->mergeCells('M3:T3');
        $objPHPExcel->getActiveSheet()->setCellValue('M4',"未税核销金额");
        $objPHPExcel->getActiveSheet()->setCellValue('N4',"有无发票");
        $objPHPExcel->getActiveSheet()->setCellValue('O4',"发票号");
        $objPHPExcel->getActiveSheet()->setCellValue('P4',"是否增值税(可抵扣)");
        $objPHPExcel->getActiveSheet()->setCellValue('Q4',"税率");
        $objPHPExcel->getActiveSheet()->setCellValue('R4',"税金");
        $objPHPExcel->getActiveSheet()->setCellValue('S4',"含税核销金额");
        $objPHPExcel->getActiveSheet()->setCellValue('T4',"实际核销金额");
         $objPHPExcel->getActiveSheet()->setCellValue('U3',"活动开始日期");
        $objPHPExcel->getActiveSheet()->mergeCells('U3:U4');
         $objPHPExcel->getActiveSheet()->setCellValue('V3',"支付方式");
        $objPHPExcel->getActiveSheet()->mergeCells('V3:V4');
        $objPHPExcel->getActiveSheet()->setCellValue('W3',"备注");
        $objPHPExcel->getActiveSheet()->mergeCells('W3:W4');

        $h=4;
         for($i=0;$i<count($applys);$i++){
        	$h++;$vo=$applys[$i];
        	 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1);
        	 $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["ahpno"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["ahpno"].'_'.$vo["he_detail_no"]);
        	  $objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$vo["apno"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$vo["apno"].'_'.$vo["detail_no"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$vo["cname"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$vo["gys_name"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$vo["brand"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$vo["kemu3"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('J'.$h,round($vo["touru"],2));
        	 $objPHPExcel->getActiveSheet()->setCellValue('K'.$h,round($vo["yihe"],2));
        	 $objPHPExcel->getActiveSheet()->setCellValue('L'.$h,round($vo["weihe"],2));
        	 $objPHPExcel->getActiveSheet()->setCellValue('M'.$h,round($vo["hexiao_weishui"],2));
        	 if($vo["has_fp"])
        	 	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,"有");
        	 else
        	 	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,"无");
        	 $objPHPExcel->getActiveSheet()->setCellValue('O'.$h,$vo["fp_no"]);
        	  if($vo["dikou"])
        	 	$objPHPExcel->getActiveSheet()->setCellValue('P'.$h,"是");
        	 else
        	 	$objPHPExcel->getActiveSheet()->setCellValue('P'.$h,"否");
        	 $objPHPExcel->getActiveSheet()->setCellValue('Q'.$h,round($vo["hexiao_shuilv"],2)."%");
        	 $objPHPExcel->getActiveSheet()->setCellValue('R'.$h,round($vo["shuijin"],2));
        	 $objPHPExcel->getActiveSheet()->setCellValue('S'.$h,round($vo["hexiao_hanshui"],2));
        	 $objPHPExcel->getActiveSheet()->setCellValue('T'.$h,round($vo["hexiao_hanshui"],2));
        	  $objPHPExcel->getActiveSheet()->setCellValue('U'.$h,$vo["start_time"]);
        	  $objPHPExcel->getActiveSheet()->setCellValue('V'.$h,$vo["zffs"]);
        	  $objPHPExcel->getActiveSheet()->setCellValue('W'.$h,$vo["beizhu"]);
        }
         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
 		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
		 $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
 		$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(20);

    	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=AP费用核销记录.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
    }
    //AP不予核销查询
    public function apcost_nocost($list=10,$page=1){
    	//费用明细
    	 $feiwhere = [];
         $feiwhere['esuseway'] = ['like','%"6"%'];
        
        $feidata = Db::table('cg_feiyongkemu3')->field('title,id')->where($feiwhere)->select();
        $this->assign("feidata",$feidata);

        $branddata=Db::table("pro_brands")->where("is_actived=1 and is_deleted=0")->field("id,brand_name")->select();
        $this->assign("branddata",$branddata);
    	$request = Request::instance();
    	$data=$request->post();
	$where="ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and apd.buyu_he=1 ";
    	if($data){
    		if($data["no"])
    			$where.="and ap.no={$data["no"]} ";
    		if(trim($data["gys_name"]))
    			$where.="and g.gys_name like '%{$data["gys_name"]}%' ";
    		if($data["pbid"])
    			$where.="and pb.id='{$data["pbid"]}' ";
    		if($data["fid"])
    			$where.="and k3.id='{$data["fid"]}' ";
    		if($data["cname"])
    			$where.="and ec.em_name='{$data["cname"]}' ";
    		if(trim($data["start"]))
    			$where.="and ap.created_on>='{$data["start"]} 00:00:00' ";
    		if(trim($data["end"]))
    			$where.="and ap.created_on<='{$data["end"]} 23:59:59' ";
    	
    	}else{
    		$data=["no"=>"","gys_name"=>"","pbid"=>"","fid"=>"","start"=>"","end"=>"","cname"=>""];
    	}

    
    	$sum=Db::table("ap_apply_detail")->alias("apd")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("gys g","apd.gys=g.id")
    	->join("pro_brands pb","apd.brand=pb.id")
    	->join("users uc","ap.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->where($where)->count();
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

    	$applys=Db::table("ap_apply_detail")->alias("apd")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("gys g","apd.gys=g.id")
    	->join("users uc","ap.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->where($where)->limit($start,$list)
    	->field("ap.id as apid,apd.id,ap.no,apd.detail_no,g.gys_name,pb.brand_name as brand,k3.title as kemu3,apd.hexiao,(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100) as weihe,ap.created_on,ec.em_name as cname")
    	->select();
    	$this->assign("applys",$applys);
    	 $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("data",$data);
    	return $this->fetch();
    }
    //导出不予核销记录
    public function nohe_excel(){

    	$where="ap.is_actived=1 and ap.is_deleted=0 and ap.is_shengxiao=1 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and apd.buyu_he=1 ";
    	$applys=Db::table("ap_apply_detail")->alias("apd")
    	->join("ap_apply ap","apd.apply_id=ap.id")
    	->join("gys g","apd.gys=g.id")
    	->join("users uc","ap.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
    	->join("pro_brands pb","apd.brand=pb.id")
    	->join("cg_feiyongkemu3 k3","apd.feiyong_id=k3.id","LEFT")
    	->where($where)
    	->field("ap.id as apid,apd.id,ap.no,apd.detail_no,g.gys_name,pb.brand_name as brand,k3.title as kemu3,apd.hexiao,(apd.pifuzonge+apd.pifuzonge*apd.shuilv/100) as weihe,ap.created_on,ec.em_name as cname")
    	->select();
    		$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
    			  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("AP费用不予核销记录");

           #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
     


          $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "AP费用不予核销记录");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:J2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

         #标题
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
       	$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);
       	   	 $objPHPExcel->getActiveSheet()->getStyle('A3:J3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:J3')->getFill()->getStartColor()->setARGB('0099CCFF');
               //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);



         $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
         $objPHPExcel->getActiveSheet()->setCellValue('B3',"原申请文件");
         $objPHPExcel->getActiveSheet()->setCellValue('C3',"原明细编号");
         $objPHPExcel->getActiveSheet()->setCellValue('D3',"申请人");
         $objPHPExcel->getActiveSheet()->setCellValue('E3',"供应商名称");
         $objPHPExcel->getActiveSheet()->setCellValue('F3',"品牌名称");
         $objPHPExcel->getActiveSheet()->setCellValue('G3',"费用明细");
         $objPHPExcel->getActiveSheet()->setCellValue('H3',"已核销金额");
         $objPHPExcel->getActiveSheet()->setCellValue('I3',"未核销金额");
         $objPHPExcel->getActiveSheet()->setCellValue('J3',"申请时间");
           $h=3;
        for($i=0;$i<count($applys);$i++){
        	$h++;$vo=$applys[$i];
        	 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1);
        	 $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["no"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["no"]."_".$vo["detail_no"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$vo["cname"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$vo["gys_name"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$vo["brand"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$vo["kemu3"]);
        	 $objPHPExcel->getActiveSheet()->setCellValue('H'.$h,round($vo["hexiao"],2));
        	 $objPHPExcel->getActiveSheet()->setCellValue('I'.$h,round($vo["weihe"],2));
        	 $objPHPExcel->getActiveSheet()->setCellValue('J'.$h,$vo["created_on"]);
        }
           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
 		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=AP费用不予核销记录.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
    }
}